﻿#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2012-01-25 - Created
 */
#endregion

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using IQ.DbBase;

namespace SqlServerAdmin
{
    public class SqlCompareTable
    {
        public delegate void Progress(int position);
        public event Progress SetMaxPosition;
        public event Progress SetCurrentPosition;
        #region # Constructor #
        public SqlCompareTable()
        { 
            ConnectionProperties con = new ConnectionProperties();
            con.SetConnectionString(DbHelper.GetConnection().ConnectionString);
            sourceServerDb = con.Server + ": " + con.Database;
            dtSource.ForceFullTable = true;
        }
        #endregion

        #region - Private variables -
        private ConnectionProperties conDest = new ConnectionProperties();
        private SqlTable dtSource = new SqlTable();
        private string sourceServerDb;
        private string tableName;
        private string lastMessage;
        private string conDestStr;
        private DataTable dtDest;
        //
        private DataTable dtChanges;
        #endregion

        #region - Private methods -
        private string getActionSql(DataRow dr, SqlConst.DataChangeAction action, bool usePK)
        {
            string sql = "";
            switch (action)
            {
                case SqlConst.DataChangeAction.Add:
                    {
                        sql = dtSource.GetInsertStatement(dr);
                        break;
                    }
                case SqlConst.DataChangeAction.Update:
                    {
                        sql = dtSource.GetUpdateStatement(dr, true);
                        break;
                    }
                case SqlConst.DataChangeAction.Delete:
                    {
                        sql = dtSource.GetDeleteSatement(dr, true);
                        break;
                    }
            }
            return sql;
        }
        private bool compareRows(DataRow src, DataRow dst)
        {
            for (int i = 0; i < src.Table.Columns.Count; ++i)
            {
                object val1 = src[src.Table.Columns[i].ColumnName];
                object val2 = dst[src.Table.Columns[i].ColumnName];
                if (src[src.Table.Columns[i].ColumnName].ToString() != dst[src.Table.Columns[i].ColumnName].ToString())
                    return false;
            }
            return true;
        }
        private void addRowToChanges(DataRow dr, SqlConst.DataChangeType change)
        {
            DataRow newDr = dtChanges.NewRow();
            for (int i = 0; i < dr.ItemArray.Length; ++i)
            {
                newDr[i] = dr[i];
            }
            newDr["ChangeType"] = (byte)change;
            dtChanges.Rows.Add(newDr);
        }
        private void compareAndAdd(DataRow src, DataRow dest)
        {
            if (!compareRows(src, dest))
            {
                addRowToChanges(src, SqlConst.DataChangeType.ChangedSource);
                addRowToChanges(dest, SqlConst.DataChangeType.ChangedDestination);
            }
        }
        private void runCompareSelect()
        {
            int totalRecords = (dtSource.ResultTable.Rows.Count > dtDest.Rows.Count ? dtSource.ResultTable.Rows.Count : dtDest.Rows.Count);
            if (SetMaxPosition != null)
                SetMaxPosition(totalRecords);
            if (SetCurrentPosition != null)
                SetCurrentPosition(0);
            // Compare source records
            DataRow drDest = null;
            for (int i = 0; i < dtSource.ResultTable.Rows.Count; ++i)
            {
                drDest = dtDest.Select(dtSource.GetWhereClause(dtSource.ResultTable.Rows[i], false)).FirstOrDefault();
                if (drDest != null)
                {
                    compareAndAdd(dtSource.ResultTable.Rows[i], drDest);
                    dtDest.Rows.Remove(drDest);
                }
                else
                    addRowToChanges(dtSource.ResultTable.Rows[i], SqlConst.DataChangeType.NotInDestination); 
                if (SetCurrentPosition != null)
                    SetCurrentPosition(i);
            }
            // Do what's left on destination
            dtDest.AcceptChanges();
            for (int i = 0; i < dtDest.Rows.Count; ++i)
            {
                addRowToChanges(dtDest.Rows[i], SqlConst.DataChangeType.NotInSource);
                if (SetCurrentPosition != null && i < totalRecords)
                    SetCurrentPosition(i);
            }
            if (SetCurrentPosition != null)
                SetCurrentPosition(totalRecords);
            dtChanges.AcceptChanges();
        }

        private void setTables()
        {
            // Get source data
            dtSource.Name = tableName;
            dtSource.OpenTableData();
            dtSource.LoadTableMetaData();
            // Put unique fields in string 
            string uqFields = String.Join(",", dtSource.UniqueFields);
            //
            dtSource.ResultTable.DefaultView.Sort = uqFields;
            // Set destination table
            dtDest = DbHelper.LoadTable(conDestStr, "SELECT * FROM " + tableName);
            dtDest.DefaultView.Sort = uqFields;
            //
            dtChanges = dtSource.ResultTable.Clone();
            DbHelper.AddDataColumn(dtChanges, "ChangeType", "System.Byte", true, 0);
            dtChanges.DefaultView.Sort = uqFields;
        }
        private DataRow getMirrorRowForChange(DataRow drSource)
        {
            SqlConst.DataChangeType changeType = (SqlConst.DataChangeType)drSource["ChangeType"];
            // Flip change type
            if (changeType == SqlConst.DataChangeType.ChangedSource)
                changeType = SqlConst.DataChangeType.ChangedDestination;
            else
                changeType = SqlConst.DataChangeType.ChangedSource;
            // Get Key value to select
            string tmpWhere = dtSource.GetWhereClause(drSource, false) + " AND ChangeType=" + (byte)changeType;
            DataRow dr = dtChanges.Select(tmpWhere).FirstOrDefault();
            return dr;
        }
        #endregion

        //#region - Protected variables -
        //#endregion

        //#region - Protected methods -
        //#endregion

        #region - Public properties / variables -
        public ConnectionProperties DestinationConnection
        {
            get { return conDest; }
            set { 
                    conDest = value;
                    conDestStr = conDest.GetConnectionString();
                }
        }
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }
        public string SourceDb
        {
            get { return sourceServerDb; }
        }
        public string DestinationDb
        {
            get { return conDest.Server + ": " + conDest.Database; }
        }
        public string SourceTableName
        {
            get { return SourceDb + " -> " + tableName; }
        }
        public string DestinationTableName
        {
            get { return DestinationDb + " -> " + tableName; }
        }
        public string LastMessage
        {
          get { return lastMessage; }
        }

        #endregion

        #region - Public methods -
        public void ChangeConnectionDb(string dbName)
        {
            conDest.Database = dbName;
            conDestStr = conDest.GetConnectionString();
        }
        public bool CompareTables(string tableName)
        {
            this.tableName = tableName;
            lastMessage = "";
            if (String.IsNullOrEmpty(conDestStr))
            {
                lastMessage = "Please connect to compare server first.";
                return false;
            }
            else
            {
                setTables();
                runCompareSelect();
                return true;
            }
        }
        public DataTable GetChanges()
        {
            return dtChanges;
        }
        public bool ActionChange(DataRow dr, SqlConst.DataChangeAction action, bool usePK)
        {
            if (dtChanges.Rows.IndexOf(dr) >= 0)
            {
                bool result = false;
                bool actionSource = false;
                string sqlAction = "";
                SqlConst.DataChangeType changeType = (SqlConst.DataChangeType)dr["ChangeType"];
                switch (changeType)
                {
                    case SqlConst.DataChangeType.ChangedSource: // action: Update S/D, Del S/D
                    case SqlConst.DataChangeType.ChangedDestination: // action: Update S/D, Del S/D
                        {
                            result = (action != SqlConst.DataChangeAction.Add);
                            actionSource = (changeType == SqlConst.DataChangeType.ChangedDestination);
                            break;
                        }
                    case SqlConst.DataChangeType.NotInDestination: // action: Add D, Del S
                    case SqlConst.DataChangeType.NotInSource: // action: Add S, Del D
                        {
                            result = (action != SqlConst.DataChangeAction.Update);
                            if ((action == SqlConst.DataChangeAction.Add && changeType == SqlConst.DataChangeType.NotInSource)
                                ||
                                (action == SqlConst.DataChangeAction.Delete && changeType == SqlConst.DataChangeType.NotInDestination))
                                actionSource = true;
                            break;
                        }
                }
                if (result)
                {
                    sqlAction = getActionSql(dr, action, usePK);
                    if (actionSource)
                        DbHelper.ExecuteSql(sqlAction);
                    else
                        DbHelper.ExecuteSql(conDestStr, sqlAction, CmdExecuteType.Scalar);
                    if (changeType == SqlConst.DataChangeType.ChangedSource || changeType == SqlConst.DataChangeType.ChangedDestination)
                    {
                        DataRow dr2 = getMirrorRowForChange(dr);
                        if (dr2 != null)
                            dtChanges.Rows.Remove(dr2);
                    }
                    dtChanges.Rows.Remove(dr);
                    dtChanges.AcceptChanges();
                }
                return result;
            }
            else
                return false;
        }
        #endregion
    }
}
